﻿CREATE PROCEDURE [dbo].[proc_ALLDepartmentBYID]
	-- Add the parameters for the stored procedure here
	@DepId int
AS
BEGIN
;WITH list as (
SELECT 0 as r,Id,bmbh,parentid, isnull(dname,'') as DName from Department WHERE			
	 id=@DepId
union all
	select r+1 as r,Department.id,Department.bmbh,Department.parentid,
	isnull(Department.dname,'')  as DName from Department,list WHERE
	Department.id =list.parentid
	and Department.bmbh <>''
	
)

select r,Id,bmbh,parentid,isnull(DName,'') as DName from list order by r desc 
END
